﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

using System.Web;

namespace NetDon.eBoss.Portal.Common
{

    public class MySQLDataBroker
    {
        private string stringconn =
            System.Configuration.ConfigurationSettings.AppSettings["fxyConnectionString"].ToString();

        private SqlConnection sqlconn = null;

        /// <summary>
        /// 数据库连接属性
        /// </summary>
        public SqlConnection SqlConn
        {
            get
            {
                if (sqlconn == null)
                {
                    sqlconn = new SqlConnection(stringconn);
                    sqlconn.Open();
                }
                else if (sqlconn.State == ConnectionState.Broken)
                {
                    sqlconn.Close();
                    sqlconn.Open();
                }
                else if (sqlconn.State == ConnectionState.Closed)
                {
                    sqlconn.Open();
                }
                else if (sqlconn.State == ConnectionState.Open)
                {
                    sqlconn.Close();
                    sqlconn.Open();
                }
                return sqlconn;
            }


        }

        /// <summary>
        /// 数据库关闭
        /// </summary>
        public void data_close()
        {
            sqlconn.Close();
        }

        /// <summary>
        /// 执行sql语句  正确 返回第一行第一列值，空值 ：Off  错误： Error  
        /// </summary>
        /// <param name="sql"> sql语句</param>
        /// <returns>返回结果字符串类型</returns>
        public string sql_data(string sql)
        {
            string sql_values = "";
            using (SqlCommand cmd = new SqlCommand(sql, SqlConn))
            {
                try
                {
                    if (cmd.ExecuteScalar() != null)
                    {
                        sql_values = cmd.ExecuteScalar().ToString();
                        sql_values = sql_values == "" ? "Off" : sql_values;
                    }
                    else
                    {
                        sql_values = "Error";
                    }

                }
                catch
                {
                    sql_values = "Error";
                }
                finally
                {
                    cmd.Dispose();
                    data_close();
                }
            }
            return sql_values;
        }

        /// <summary>
        /// 执行sql语句  正确 返回第一行第一列值，空值 ：Off  错误： Error  
        /// </summary>
        /// <param name="sql">sql 语句</param>
        /// <param name="parame">SqlParameter sql参数变量</param>
        /// <returns></returns>
        public string sql_data(string sql, SqlParameter parame)
        {
            string sql_values = "";
            using (SqlCommand cmd = new SqlCommand(sql, SqlConn))
            {
                DataSet ds = new DataSet();
                cmd.Parameters.Add(parame);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                try
                {
                    da.Fill(ds, "tablename");
                    if (ds != null && ds.Tables["tablename"] != null)
                    {
                        sql_values = ds.Tables["tablename"].Rows[0][0].ToString();
                        sql_values = sql_values == "" ? "Off" : sql_values;
                    }
                }
                catch
                {
                    sql_values = "Error";
                }
                finally
                {
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                    data_close();
                }

            }
            return sql_values;

        }


        /// <summary>
        /// 执行sql语句  正确 返回第一行第一列值，空值 ：Off  错误： Error  
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parames">SqlParameter sql参数变量数组</param>
        /// <returns></returns>
        public string sql_data(string sql, SqlParameter[] parames)
        {
            string sql_values = "";
            using (SqlCommand cmd = new SqlCommand(sql, SqlConn))
            {
                DataSet ds = new DataSet();
                foreach (SqlParameter parame in parames)
                {
                    cmd.Parameters.Add(parame);

                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                try
                {
                    da.Fill(ds, "tablename");
                    if (ds != null && ds.Tables["tablename"] != null)
                    {
                        sql_values = ds.Tables["tablename"].Rows[0][0].ToString();
                        sql_values = sql_values == "" ? "Off" : sql_values;
                    }
                }
                catch
                {
                    sql_values = "Error";
                }
                finally
                {
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                    data_close();
                }
            }
            return sql_values;

        }


        /// <summary>
        /// 返回 dataset 数据集 错误：空数据集。
        /// </summary>
        /// <param name="sql">sql</param>
        /// <returns>DataSet ds</returns>
        public DataSet sql_dataset(string sql)
        {
            DataSet ds = new DataSet();
            using (SqlDataAdapter da = new SqlDataAdapter(sql, SqlConn))
                try
                {
                    da.Fill(ds, "tablename");

                }
                catch
                {
                    ds = new DataSet();
                }
                finally
                {
                    da.Dispose();
                    data_close();
                }
            return ds;
        }

        /// <summary>
        /// 返回 dataset 数据集 错误：空数据集。
        /// </summary>
        /// <param name="sql">sql</param>
        /// <param name="parame">SqlParameter sql语句参数 </param>
        /// <returns>DataSet ds</returns>
        public DataSet sql_dataset(string sql, SqlParameter parame)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            using (SqlCommand cmd = new SqlCommand(sql, SqlConn))
                try
                {
                    cmd.Parameters.Add(parame);
                    da = new SqlDataAdapter(cmd);

                    da.Fill(ds, "tablename");

                }
                catch
                {
                    ds = new DataSet();
                }
                finally
                {
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                    da.Dispose();
                    data_close();
                }
            return ds;
        }

        /// <summary>
        /// 返回 dataset 数据集 错误：空数据集。
        /// </summary>
        /// <param name="sql">sql</param>
        /// <param name="parames">SqlParameter sql语句参数数组 </param>
        /// <returns>DataSet ds</returns>
        public DataSet sql_dataset(string sql, SqlParameter[] parames)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            using (SqlCommand cmd = new SqlCommand(sql, SqlConn))
                try
                {
                    foreach (SqlParameter parame in parames)
                    {
                        cmd.Parameters.Add(parame);
                    }

                    da = new SqlDataAdapter(cmd);
                    da.Fill(ds, "tablename");

                }
                catch
                {
                    ds = new DataSet();
                }
                finally
                {
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                    da.Dispose();
                    data_close();
                }
            return ds;
        }

        public int update(string sql)
        {
            int intbl = 0;
            SqlCommand cmd = new SqlCommand(sql, SqlConn);
            try
            {
                if (cmd.ExecuteNonQuery() >= 1)
                {
                    intbl = 1;

                }
                else
                {
                    intbl = -1;
                }
            }
            catch
            {
                intbl = -1;
            }
            finally
            {
                cmd.Dispose();
                data_close();
            }
            return intbl;
        }




        public string update_sql(string sql, SqlParameter[] parames)
        {
            string intbl = "0";
            SqlCommand cmd = new SqlCommand(sql, SqlConn);
            try
            {
                DataSet ds = new DataSet();
                foreach (SqlParameter parame in parames)
                {
                    cmd.Parameters.Add(parame);
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);

                if (cmd.ExecuteNonQuery() >= 1)
                {
                    intbl = "1";

                }
                else
                {
                    intbl = "0";
                }
            }
            catch (Exception e)
            {
                intbl = e.ToString();
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Dispose();
                data_close();
            }
            return intbl;
        }




    }
}